package cn.thornbird.orgsync.util;

import cn.thornbird.orgsync.model.Column;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;

@Slf4j
public class ExcelUtil {

    public static void addComment(SXSSFSheet sheet, String text, int row, int column) {
        SXSSFDrawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, column, row, column, row));
        comment.setString(new XSSFRichTextString(text));
        sheet.getRow(row).getCell(column).setCellComment(comment);
    }

    public static SXSSFWorkbook createWorkbook() {
        return new SXSSFWorkbook(1000);
    }

    public static SXSSFWorkbook createWorkbook(int rowAccessWindowSize) {
        return new SXSSFWorkbook(rowAccessWindowSize);
    }

    public static Workbook createWorkbook(String name, List<Column> columns,
                                          List<Map<String, Object>> values) {
        SXSSFWorkbook workbook = createWorkbook();
        createSheet(workbook, name, columns, values);
        return workbook;
    }

    public static SXSSFSheet createSheet(SXSSFWorkbook workbook, String name, List<Column> columns,
                                         List<Map<String, Object>> values) {
        return createSheet(workbook, name, columns, values, 0);
    }

    public static SXSSFSheet createSheet(SXSSFWorkbook workbook, String name, List<Column> columns,
                                         List<Map<String, Object>> values, int mode) {
        SXSSFSheet sheet = workbook.createSheet(name);
        sheet.createFreezePane(0, 1, 0, 1);

        Row headerRow = sheet.createRow(0);
        List<CellStyle> styles = new ArrayList<>();
        int columnCount = columns.size();
        for (int i = 0; i < columnCount; i++) {
            Column column = columns.get(i);
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(column.getHorizontalAlignment());
            style.setVerticalAlignment(column.getVerticalAlignment());
            style.setWrapText(column.isWrapText());
            if (column.getFormat() != null && !column.getFormat().isEmpty()) {
                DataFormat dataFormat = workbook.createDataFormat();
                style.setDataFormat(dataFormat.getFormat(column.getFormat()));
            }
            styles.add(style);

            Cell headerCell = headerRow.createCell(i);
            if (column.getWidth() > 0) {
                sheet.setColumnWidth(i, column.getWidth() * 256);
            } else {
                sheet.setColumnWidth(i,100);
            }
            headerCell.setCellValue(column.getTitle());
            headerCell.setCellStyle(style);
        }

        int count = values.size();
        for (int i = 0; i < count; i++) {
            Map<String, Object> record = values.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columnCount; j++) {
                Column column = columns.get(j);
                CellStyle style = styles.get(j);
                Object value = record.get(column.getKey());
                Cell cell = row.createCell(j);
                if (value == null) {
                    cell.setCellValue("");
                } else if (value instanceof Double || value instanceof Float ||
                        value instanceof Long || value instanceof Integer) {
                    cell.setCellValue(Double.parseDouble(String.valueOf(value)));
                } else if (value instanceof Date) {
                    cell.setCellValue((Date) value);
                } else if (value instanceof Boolean) {
                    if (mode == 1) {
                        cell.setCellValue(Boolean.parseBoolean(String.valueOf(value)) ? "Yes" : "No");
                    } else {
                        cell.setCellValue(Boolean.parseBoolean(String.valueOf(value)) ? "是" : "否");
                    }
                } else {
                    cell.setCellValue(String.valueOf(value));
                }
                cell.setCellStyle(style);
            }
        }

        sheet.trackAllColumnsForAutoSizing();
        for (int i = 0; i < columnCount; i++) {
            Column column = columns.get(i);
            if (column.getWidth() <= 0) {
                sheet.autoSizeColumn(i);
            }
        }

        return sheet;
    }

    public static SXSSFSheet createSheet(SXSSFWorkbook workbook, String name, List<Column> columns,
                                         List<Map<String, Object>> values, int groupColumnIndex, boolean collapse) {
        SXSSFSheet sheet = createSheet(workbook, name, columns, values);
        int startIndex = -1;
        int endIndex = -1;
        for (int i = 0, lastIndex = sheet.getLastRowNum(); i <= lastIndex; i++) {
            SXSSFCell cell = sheet.getRow(i).getCell(groupColumnIndex);
            boolean empty = cell == null || cell.getStringCellValue() == null || cell.getStringCellValue().isEmpty();
            if (startIndex < 0) {
                if (empty) {
                    startIndex = i;
                }
            } else {
                if (empty) {
                    if (i == lastIndex) {
                        endIndex = i;
                    }
                } else {
                    endIndex = i - 1;
                }
            }
            if (startIndex >= 0 && endIndex >= 0) {
                sheet.groupRow(startIndex, endIndex);
                sheet.setRowGroupCollapsed(startIndex, collapse);
                startIndex = -1;
                endIndex = -1;
            }
        }
        return sheet;
    }

    public static void export(HttpServletResponse response, Workbook workbook, String name) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        String excelFileName = name;
        if (!name.contains(".")) {
            excelFileName = name + ".xlsx";
        }
        String outputFileName = excelFileName;
        try {
            outputFileName = URLEncoder.encode(excelFileName, StandardCharsets.UTF_8.name());
        } catch (UnsupportedEncodingException e) {
            log.error("error", e);
        }
        response.addHeader("Content-Disposition", String.format("attachment;filename=%s;filename*=%s",
                outputFileName, StandardCharsets.UTF_8.name()));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    public static void export(HttpServletResponse response, String name, List<Column> columns,
                              List<Map<String, Object>> values) throws IOException {
        Workbook workbook = createWorkbook(name, columns, values);
        export(response, workbook, name);
    }

    public static void export(HttpServletResponse response, String filePath) throws IOException {
        File file = new File(filePath);
        Workbook workbook;
        String filename = file.getName();
        if (file.exists()) {
            XSSFWorkbookFactory factory = new XSSFWorkbookFactory();
            InputStream inputStream = Files.newInputStream(Paths.get(filePath));
            workbook = factory.create(inputStream);
            inputStream.close();
        } else {
            workbook = createWorkbook("Sheet1", new ArrayList<>(), new ArrayList<>());
        }
        export(response, workbook, filename);
    }

    public static List<List<String>> importExcel(String filename, List<Integer> stringIndexes)
            throws IOException, InvalidFormatException {
        List<List<String>> list = new ArrayList<>();
        File file = new File(filename);
        if (!file.exists()) {
            return list;
        }

        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator(workbook);
        XSSFRow headerRow = sheet.getRow(0);
        int columnCount = headerRow.getPhysicalNumberOfCells();
        int rowCount = sheet.getLastRowNum();
        for (int i = 1; i <= rowCount; i++) {
            List<String> record = new ArrayList<>();
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            for (int j = 0; j < columnCount; j++) {
                String value = null;
                XSSFCell cell = row.getCell((short) j);
                if (cell != null) {
                    CellType type = cell.getCellType();
                    if (stringIndexes != null && stringIndexes.contains(j)) {
                        if (type == CellType.STRING) {
                            value = cell.getStringCellValue();
                        } else {
                            value = cell.getRawValue();
                        }
                    } else {
                        switch (type) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    value = DateUtil.formatDate(date, DateUtil.DATE_FORMAT);
                                } else {
                                    value = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
                                }
                                break;
                            case BOOLEAN:
                                value = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                CellValue cellValue = formulaEvaluator.evaluate(cell);
                                CellType cellType = cellValue.getCellType();
                                switch (cellType) {
                                    case STRING:
                                        value = cellValue.getStringValue();
                                        break;
                                    case NUMERIC:
                                        value = String.valueOf(cellValue.getNumberValue());
                                        break;
                                    case BOOLEAN:
                                        value = String.valueOf(cellValue.getBooleanValue());
                                        break;
                                    default:
                                        break;
                                }
                                break;
                            case _NONE:
                            case ERROR:
                            case BLANK:
                            default:
                                break;
                        }
                    }
                }
                value = value == null ? "" : value.trim();
                record.add(value);
            }
            list.add(record);
        }
        return list;
    }

    public static List<Map<String, String>> importFromExcel(String filename, List<Integer> stringIndexes)
            throws IOException, InvalidFormatException {
        List<Map<String, String>> list = new ArrayList<>();
        File file = new File(filename);
        if (!file.exists()) {
            return list;
        }

        List<String> headers = new ArrayList<>();
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow headerRow = sheet.getRow(0);
        int columnCount = headerRow.getPhysicalNumberOfCells();
        for (int i = 0; i < columnCount; i++) {
            XSSFCell cell = headerRow.getCell((short) i);
            CellType type = cell.getCellType();
            String header;
            if (type == CellType.STRING) {
                header = cell.getStringCellValue();
            } else {
                header = cell.getRawValue();
            }
            headers.add((header == null ? "" : header).trim());
        }

        List<List<String>> records = importExcel(filename, stringIndexes);
        for (List<String> record : records) {
            Map<String, String> values = new HashMap<>();
            for (int i = 0; i < columnCount; i++) {
                String value = null;
                if (i < record.size()) {
                    value = record.get(i);
                }
                values.put(headers.get(i), value);
            }
            list.add(values);
        }
        return list;
    }

    public static void writeMessages(String filename, List<String> messages) throws IOException {
        File file = new File(filename);
        if (!file.exists() || messages == null || messages.isEmpty()) {
            return;
        }

        Path path = Paths.get(filename);
        XSSFWorkbookFactory factory = new XSSFWorkbookFactory();
        InputStream inputStream = Files.newInputStream(path);
        Workbook workbook = factory.create(inputStream);
        inputStream.close();
        Sheet sheet = workbook.getSheetAt(0);
        Row headerRow = sheet.getRow(0);
        int columnIndex = headerRow.getPhysicalNumberOfCells();
        int count = messages.size();
        for (int i = 0; i < count; i++) {
            Row row = sheet.getRow(i + 1);
            String message = messages.get(i);
            message = message == null ? "" : message;
            if (row == null) {
                continue;
            }
            Cell cell = row.getCell((short) columnIndex);
            if (cell == null) {
                cell = row.createCell(columnIndex);
            }
            cell.setCellValue(message);
        }
        OutputStream outputStream = Files.newOutputStream(path);
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

}
